Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Tablespaces and Data Files

The database is at the highest level and is divided into one or more logical pieces known as tablespaces. A tablespace is used to logically group data together. For example, you can have a tablespace for accounting and a separate tablespace for purchasing. Segmenting groups into different tablespaces simplifies the administration of these groups (see Figure 1.2). Tablespaces are made up of one or more data files. By using more than one data file per tablespace, you can spread the data over many different disks to distribute the I/O load and improve performance.


Figure 1.2  The relationship between the database, tablespaces, and data files.

As part of the process of creating the database, Oracle automatically creates the SYSTEM tablespace for you. Although a small database can fit within the SYSTEM tablespace, it is recommended that you create a separate tablespace for user data. The SYSTEM tablespace is where the data dictionary is kept. The data dictionary contains information about tables, indexes, clusters, and so on.

The data files can be operating system files; in the case of some operating systems, the data files can be raw devices. Data files and data access methods are described in detail in Chapter 12, “Operating System-Specific Tuning.”

Database Schema

The database schema is a collection of logical structures that define how you see the database’s data. These logical structures are known as schema objects. Schema objects are made up of structures such as tables, clusters, indexes, views, stored procedures, database triggers, and sequences.

  Table. A table is the basic logical storage unit in the Oracle database. A table is made up of a table name and rows and columns of data. The columns are defined by name and data type. A table is stored within a tablespace. It is common for many tables to share a tablespace.
  Cluster. A cluster is a set of tables physically stored together as one table. If data in two or more tables is frequently retrieved together and closely related, using a clustered table can be quite efficient. Tables can be accessed separately even though they are part of a clustered table. Because of the structure of the cluster, if related data is accessed simultaneously, it requires much less I/O overhead.
  Index. An index is a structure created to help retrieve data more quickly and efficiently (just as the index in this book allows you to find a particular section more quickly).
  Views. A view is a window into one or more tables. A view does not store any data; it is used for presentation of table data. A view can be queried, updated, and deleted as a table without restriction.
Views are typically used to simplify the user’s perception of data access by providing information from several tables transparently. Views can also be used to prevent some data from being accessed by the user or to create a join from multiple tables.
  Stored procedures. Stored procedures are predefined SQL queries stored in the data dictionary designed to allow more efficient queries. By using stored procedures, you can reduce the amount of information that must be passed to the RDBMS and thus reduce network traffic and improve performance.

Segments, Extents, and Data Blocks

Within Oracle, the space used to store data is controlled by the use of logical structures. These structures consist of segments, extents, and data blocks. A segment is a set of extents used to store a particular type of data. Segments in turn are made up of collections of pieces called extents; in turn, extents are made up of pieces called data blocks (see Figure 1.3). A block is the smallest unit of storage in an Oracle database. The database block contains header information concerning the block itself as well as the data.


Figure 1.3  Segments, extents, and data blocks.

A segment is a group of extents used for storing a particular type of data within the database. An Oracle database can use four different types of segments:

  Data segment: Stores user data within the database.
  Index segment: Stores indexes.
  Rollback segment: Stores rollback information used when data must be rolled back.
  Temporary segment: Temporary segments are created when an SQL statement needs a temporary work area; they are destroyed when the SQL statement is finished. These segments are used during various database operations such as sorts.

Extents are the building blocks used to create segments; extents are made up of data blocks. An extent is used to minimize the amount of wasted (empty) storage. As more and more data is input into tablespaces in your database, the extents used for storing that data can grow or shrink depending on need. In this manner, many tablespaces can share the same storage space without preallocating the divisions between those tablespaces.

At tablespace creation time, you can specify the minimum number of extents to allocate as well as the number of extents to add at a time when that allocation has been used up. This arrangement gives you efficient control over the space used in your database.

Data blocks are the smallest pieces that make up an Oracle database; they are physically stored on disk. Although the data block in most systems is 2K (2,04bytes), you can change this size for efficiency depending on your application or operating system. Sizing the data block is described in detail in Chapter 10, “Performance Enhancements.”

The Oracle Instance

The Oracle instance consists of the Oracle processes and shared memory necessary to access information in the database. The instance is made up of the user processes, the Oracle background processes, and the shared memory used by these processes (see Figure 1.4).


Figure 1.4  The Oracle instance.

The following sections look at the various pieces that make up the Oracle instance, starting with the shared memory and continuing with the various Oracle processes.

The Oracle Memory Structure

Oracle uses shared memory for several purposes, including caching of data and indexes as well as storing shared program code. This shared memory is used for several functions and is broken into various pieces, or memory structures. The basic memory structures associated with Oracle are the System Global Area (SGA) and the Program Global Area (PGA).


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.